# 安装相关库
# pip install xlwt
# pip install xlutils
# pip install xlrd
# pip install openpyxl

import os
# import numpy as np
from openpyxl import load_workbook
class MxlsxWB:
  def init(self,workparth=os.getcwd(),filename=None):
    self.workpath=workparth
    self.filename=filename
  def set_path(self,workpath):
    self.workpath=workpath
    os.chdir(self.workpath)
  def set_filename(self,filename=None):
    self.filename=filename
  def get_fileinfo(self):
    print(self.filename)
    print("="*30,"文件信息","="*30)
    self.wb=load_workbook(filename=self.filename)
    self.sheetnames=self.wb.sheetnames
    print("文件"+self.filename+"共有",len(self.sheetnames),"个sheet页")
    print("表名为：")
    for name in self.sheetnames:
      print(name,end= " ")
    print()
    print("="*30,"文件信息结束","="*30)
  # 选择工作表
  def choose_sheet(self,sheetname = None):
    if sheetname == None:
      self.sheetname=self.sheetnames[0]
    else:
      self.sheetname=sheetname
    self.worksheet=self.wb[self.sheetname]

  # 获取工作表信息
  def get_sheetinfo(self):
    print("="*30,self.sheetname,"="*30)
    self.num_of_rows=len(list(self.worksheet.rows))
    self.num_of_cols=len(list(self.worksheet.columns))

    print("行数:",self.num_of_rows)
    print("列数:",self.num_of_cols)
    print("列名:",MxlsxWB.get_rowdata(self,rownum=1))
    print("="*30,self.sheetname,"="*30)
  
  # 获取单行数据
  def get_rowdata(self,rownum):
    rowdata=[]
    for row in self.worksheet.iter_rows(min_row=rownum,max_row=rownum,max_col=self.num_of_cols):
      for cell in row:
        rowdata.append(cell.value)
    return rowdata
  
  # 获取单列数据
  def get_coldata(self,colnum):
    coldata=[]
    for col in self.worksheet.iter_cols(min_col=colnum,max_col=colnum,max_row=self.num_of_rows):
      for cell in col:
        coldata.append(cell.value)
    return coldata
  # 获取工作表总行数
  def get_rows_of_sheet(self):
    return len(list(self.worksheet.rows))
  # 获取工作表总列数
  def get_cols_of_sheet(self):
    return len(list(self.worksheet.columns))
  
  # 获取具有某个字符串的行号
  def get_row_number_of_sheet(self,rownum,col_name):
    for row in self.worksheet.iter_rows(min_row=1,max_row=rownum,max_col=self.num_of_cols):
      for cell in row:
        if cell.value == col_name:
          return cell.row
  # 获取具有某个字符串的列号
  def get_col_number_of_sheet(self,rownum,col_name):
    for row in self.worksheet.iter_rows(min_row=1,max_row=rownum,max_col=self.num_of_cols):
      for cell in row:
        if cell.value == col_name:
          return cell.column
  # 获取指定列指定值的行号，比如在学号列中查找学号2所在的行号
  def get_row_number_of_appoint_value(self,rownum,col_name,col_value):
    # 找到指定col_name列的列号
    col_number=self.get_col_number_of_sheet(rownum,col_name)
    for row in self.worksheet.iter_cols(min_col=col_number,max_col=col_number,max_row=self.num_of_rows):
      for cell in row:
        if cell.value == col_value:
          return self.get_rowdata(cell.row)
  
  # 获取单列数据，指定开始结束行号
  def get_coldata_with_rownum(self,colnum,rownum_start,rownum_end):
    coldata=[]
    for col in self.worksheet.iter_cols(min_col=colnum,max_col=colnum,min_row=rownum_start,max_row=rownum_end):
      for cell in col:
        coldata.append(cell.value)
    return coldata
  
  # 获取特定区域数据
  def get_area_data(self, rownum_start=1, rownum_end=None, colnum_start=1, colnum_end=None):
    print("="*30, "区域数据", "="*30)
    areadata = []
    
    # 如果未指定结束行或结束列，则使用工作表的最大行数和列数
    if rownum_end is None:
        rownum_end = self.get_rows_of_sheet()
    if colnum_end is None:
        colnum_end = self.get_cols_of_sheet()

    for row in self.worksheet.iter_rows(min_row=rownum_start, max_row=rownum_end, min_col=colnum_start, max_col=colnum_end):
        # 创建一个新列表来保存当前行的数据
        rowdata = [cell.value for cell in row]
        # 将当前行的数据添加到areadata中
        areadata.append(rowdata)
    return areadata

